﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace GZ.Entity
{
    /// <summary>
    /// 订单实体类
    /// </summary>
    public class EOrder
    {
        public EOrder()
        {
            this.actualamount = 0;
            this.address = string.Empty;
            this.businessdistrictid = 0;
            this.customername = string.Empty;
            this.discounttypeid = 0;
            this.email = string.Empty;
            this.floorid = 0;
            this.isdelivery = 1;
            this.officeid = 0;
            this.orderamount = 0;
            this.orderstate = 0;
            this.ordertype = 1;
            this.regionid = 0;
            this.telnum = string.Empty;
            this.paymenttype = 1;
            this.deliveryremark = string.Empty;
        }

        #region 属性
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        private Int64 orderid;

        public Int64 OrderId
        {
            get { return orderid; }
            set { orderid = value; }
        }

        private string customername;

        public string CustomerName
        {
            get { return customername; }
            set { customername = value; }
        }
        private string email;

        public string Email
        {
            get { return email; }
            set { email = value; }
        }
        private string telnum;

        public string TelNum
        {
            get { return telnum; }
            set { telnum = value; }
        }
        private int regionid;

        public int RegionId
        {
            get { return regionid; }
            set { regionid = value; }
        }
        private int businessdistrictid;

        public int BusinessDistrictId
        {
            get { return businessdistrictid; }
            set { businessdistrictid = value; }
        }
        private int officeid;

        public int OfficeId
        {
            get { return officeid; }
            set { officeid = value; }
        }
        private int floorid;

        public int FloorId
        {
            get { return floorid; }
            set { floorid = value; }
        }
        private string address;

        public string Address
        {
            get { return address; }
            set { address = value; }
        }
        private int ordertype;

        public int OrderType
        {
            get { return ordertype; }
            set { ordertype = value; }
        }
        private int isdelivery;

        public int IsDelivery
        {
            get { return isdelivery; }
            set { isdelivery = value; }
        }
        private decimal orderamount;

        public decimal OrderAmount
        {
            get { return orderamount; }
            set { orderamount = value; }
        }
        private decimal actualamount;

        public decimal ActualAmount
        {
            get { return actualamount; }
            set { actualamount = value; }
        }
        private int discounttypeid;

        public int DiscountTypeId
        {
            get { return discounttypeid; }
            set { discounttypeid = value; }
        }
        private int orderstate;

        public int OrderState
        {
            get { return orderstate; }
            set { orderstate = value; }
        }

        private int deliveryperiodId;

        public int DeliveryPeriodId
        {
            get { return deliveryperiodId; }
            set { deliveryperiodId = value; }
        }

        private DateTime deliverydate;

        public DateTime DeliveryDate
        {
            get { return deliverydate; }
            set { deliverydate = value; }
        }

        public string Memo
        {
            set;
            get;
        }
        public DateTime CreateDate
        { set; get; }
        public int CustomerId
        {
            set;
            get;
        }

        private int paymenttype;

        public int PaymentType
        {
            get { return paymenttype; }
            set { paymenttype = value; }
        }

        private string deliveryremark;

        public string DeliveryRemark
        {
            get { return deliveryremark; }
            set { deliveryremark = value; }
        }

        private Int16 ispay;

        public Int16 IsPay
        {
            get { return ispay; }
            set { ispay = value; }
        }

        #endregion

        #region

        /// <summary>
        /// 插入订单
        /// </summary>
        /// <param name="obj">订单对象</param>
        /// <returns>返回订单Id，0为插入失败</returns>
        public Int64 Insert(EOrder obj)
        {
            #region  sql语句
            string sql = @"INSERT INTO tb_order
                            ( OrderId,
                            CustomerName,
                            Email,
                            TelNum,
                            RegionId,
                            BusinessDistrictId,
                            OfficeId,
                            FloorId,
                            Address,
                            OrderType,
                            IsDelivery,
                            OrderAmount,
                            ActualAmount,
                            DiscountTypeId,
                            OrderState,
                            CreateDate,
                            Memo,
                            DeliveryPeriodId,
                            DeliveryDate,
                            CustomerId,
                            PaymentType
                            )
                            VALUES
                            ( @OrderId,
                            @CustomerName,
                            @Email,
                            @TelNum,
                            @RegionId,
                            @BusinessDistrictId,
                            @OfficeId,
                            @FloorId,
                            @Address,
                            @OrderType,
                            @IsDelivery,
                            @OrderAmount,
                            @ActualAmount,
                            @DiscountTypeId,
                            @OrderState,
                            @CreateDate,
                            @Memo,
                            @DeliveryPeriodId,
                            @DeliveryDate,
                            @CustomerId,
                            @PaymentType
                            );";
            #endregion
            //获取订单Id
            Int64 OrderId = GZ.Common.IdentifyHelper.Instance.GetOrderId();
            try
            {
                using (MySqlConnection conn = new MySqlConnection(GZ.DAL.MySQLHelper.connStr))
                {
                    conn.Open();
                    MySqlTransaction trans = conn.BeginTransaction();
                    try
                    {
                        MySqlCommand myCommand = conn.CreateCommand();
                        myCommand.CommandText = sql;
                        myCommand.Transaction = trans;
                        #region 订单参数
                        obj.OrderState = 1;
                        myCommand.Parameters.Add(new MySqlParameter("@OrderId", OrderId));
                        myCommand.Parameters.Add(new MySqlParameter("@CustomerName", obj.CustomerName));
                        myCommand.Parameters.Add(new MySqlParameter("@Email", obj.Email));
                        myCommand.Parameters.Add(new MySqlParameter("@TelNum", obj.TelNum));
                        myCommand.Parameters.Add(new MySqlParameter("@RegionId", obj.RegionId));
                        myCommand.Parameters.Add(new MySqlParameter("@BusinessDistrictId", obj.BusinessDistrictId));
                        myCommand.Parameters.Add(new MySqlParameter("@OfficeId", obj.OfficeId));
                        myCommand.Parameters.Add(new MySqlParameter("@FloorId", obj.FloorId));
                        myCommand.Parameters.Add(new MySqlParameter("@Address", obj.Address));
                        myCommand.Parameters.Add(new MySqlParameter("@OrderType", obj.OrderType));
                        myCommand.Parameters.Add(new MySqlParameter("@IsDelivery", obj.IsDelivery));
                        myCommand.Parameters.Add(new MySqlParameter("@OrderAmount", obj.OrderAmount));
                        myCommand.Parameters.Add(new MySqlParameter("@ActualAmount", obj.ActualAmount));
                        myCommand.Parameters.Add(new MySqlParameter("@DiscountTypeId", obj.DiscountTypeId));
                        myCommand.Parameters.Add(new MySqlParameter("@OrderState", obj.OrderState));
                        myCommand.Parameters.Add(new MySqlParameter("@CreateDate", DateTime.Now));
                        myCommand.Parameters.Add(new MySqlParameter("@Memo", obj.Memo));
                        myCommand.Parameters.Add(new MySqlParameter("@DeliveryPeriodId", obj.DeliveryPeriodId));
                        myCommand.Parameters.Add(new MySqlParameter("@DeliveryDate", obj.DeliveryDate));
                        myCommand.Parameters.Add(new MySqlParameter("@CustomerId", obj.CustomerId));
                        myCommand.Parameters.Add(new MySqlParameter("@PaymentType", obj.PaymentType)); 
                        #endregion
                        myCommand.ExecuteNonQuery();
                        trans.Commit();
                        conn.Close();
                    }
                    catch (Exception mysqlex)
                    {
                        trans.Rollback();
                        conn.Close();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return OrderId;
        }

        /// <summary>
        /// 根据下单时间区间查询订单列表
        /// </summary>
        /// <param name="BeginDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <param name="CustomerId">客户Id</param>
        /// <returns>订单列表</returns>
        public DataTable GetOrdersByCreateDate(DateTime BeginDate, DateTime EndDate, int CustomerId)
        {
            string sql = "SELECT * FROM tb_order WHERE CreateDate>=@BeginDate AND CreateDate<=@EndDate AND CustomerId =@CustomerId AND IsDelete=0";
            MySqlParameter[] parameters = new MySqlParameter[3];
            parameters[0] = new MySqlParameter("@BeginDate", BeginDate);
            parameters[1] = new MySqlParameter("@EndDate", EndDate.AddDays(1).AddMilliseconds(-2));
            parameters[2] = new MySqlParameter("@CustomerId", CustomerId);
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];

        }


        /// <summary>
        /// 根据订单参数条件查询
        /// </summary>
        /// <param name="obj">订单参数</param>
        /// <param name="BeginDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">页码</param>
        /// <param name="DateType">日期查找区间</param>
        /// <returns>订单列表</returns>
        public DataTable Find(EOrder obj, DateTime BeginDate, DateTime EndDate, int PageIndex, int PageSize, string DateType)
        {
            DataTable dt = new DataTable();
            StringBuilder sqlPager = new StringBuilder(@"SELECT  Id,OrderId,CustomerId,CustomerName,TelNum,
       (select DeliveryAreaName from tb_deliveryarea where Id=RegionId) as RegionId,
       (select DeliveryAreaName from tb_deliveryarea where Id=BusinessDistrictId) as BusinessDistrictId,
       (select DeliveryAreaName from tb_deliveryarea where Id=OfficeId) as OfficeId,
       (select DeliveryAreaName from tb_deliveryarea where Id=FloorId) as FloorId,
       Address,OrderType,IsDelivery,OrderAmount,ActualAmount,DiscountTypeId,OrderState,CreateDate,
       Memo,DeliveryPeriodId,DeliveryDate,ActualDeliveryDate,IsDelete,PaymentType,DeliveryRemark,(SELECT DeliveryPeriodName FROM tb_deliveryperiod WHERE Id=DeliveryPeriodId) AS DeliveryPeriodName,IsPay FROM tb_order WHERE 1=1 ");
            using (MySqlConnection conn = new MySqlConnection(GZ.DAL.MySQLHelper.connStr))
            {
                MySqlCommand cmd = new MySqlCommand();
                if (obj != null)
                {
                    if (obj.OrderId > 0)
                    {
                        sqlPager.Append(" AND OrderId=@OrderId");
                        cmd.Parameters.AddWithValue("@OrderId", obj.OrderId);
                    }
                    if (obj.CustomerName.Length > 0)
                    {
                        sqlPager.Append(" AND CustomerName=@CustomerName");
                        cmd.Parameters.AddWithValue("@CustomerName", obj.CustomerName);
                    }
                    if (obj.Email.Length > 0)
                    {
                        sqlPager.Append(" AND Email=@Email");
                        cmd.Parameters.AddWithValue("@Email", obj.Email);
                    }
                    if (obj.TelNum.Length > 0)
                    {
                        sqlPager.Append(" AND TelNum=@TelNum");
                        cmd.Parameters.AddWithValue("@TelNum", obj.TelNum);
                    }
                    if (obj.IsDelivery > 0)
                    {
                        sqlPager.Append(" AND IsDelivery=@IsDelivery");
                        cmd.Parameters.AddWithValue("@IsDelivery", obj.IsDelivery);
                    }
                    if (obj.OrderType > 0)
                    {
                        sqlPager.Append(" AND OrderType=@OrderType");
                        cmd.Parameters.AddWithValue("@OrderType", obj.OrderType);
                    }
                    if (obj.OrderState > 0)
                    {
                        sqlPager.Append(" AND OrderState=@OrderState");
                        cmd.Parameters.AddWithValue("@OrderState", obj.OrderState);
                    }
                    if (obj.PaymentType > 0)
                    {
                        sqlPager.Append(" AND PaymentType=@PaymentType");
                        cmd.Parameters.AddWithValue("@PaymentType", obj.PaymentType);
                    }
                     
                    sqlPager.Append(" AND " + DateType + " BETWEEN @BeginDate AND @EndDate");
                    cmd.Parameters.AddWithValue("@BeginDate", BeginDate);
                    cmd.Parameters.AddWithValue("@EndDate", EndDate.AddDays(1).AddMilliseconds(-2));
                }
                sqlPager.Append(" Order By CREATEDATE desc");
                sqlPager.Append(" LIMIT " + PageIndex + "," + PageSize);

                cmd.CommandText = sqlPager.ToString();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                da.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 根据订单参数条件返回总数
        /// </summary>
        /// <param name="obj">订单参数</param>
        /// <param name="BeginDate">开始时间</param>
        /// <param name="EndDate">结束时间</param>
        /// <param name="DateType">日期查询区间类型</param>
        /// <returns></returns>
        public int FindRowCount(EOrder obj, DateTime BeginDate, DateTime EndDate, string DateType)
        {
            StringBuilder sqlRowCount = new StringBuilder("SELECT count(1) FROM tb_order WHERE 1=1 ");
            using (MySqlConnection conn = new MySqlConnection(GZ.DAL.MySQLHelper.connStr))
            {
                MySqlCommand cmd = new MySqlCommand();
                if (obj != null)
                {
                    if (obj.OrderId > 0)
                    {
                        sqlRowCount.Append(" AND OrderId=@OrderId");
                        cmd.Parameters.AddWithValue("@OrderId", obj.OrderId);
                    }
                    if (obj.CustomerName.Length > 0)
                    {
                        sqlRowCount.Append(" AND CustomerName=@CustomerName");
                        cmd.Parameters.AddWithValue("@CustomerName", obj.CustomerName);
                    }
                    if (obj.Email.Length > 0)
                    {
                        sqlRowCount.Append(" AND Email=@Email");
                        cmd.Parameters.AddWithValue("@Email", obj.Email);
                    }
                    if (obj.TelNum.Length > 0)
                    {
                        sqlRowCount.Append(" AND TelNum=@TelNum");
                        cmd.Parameters.AddWithValue("@TelNum", obj.TelNum);
                    }
                    if (obj.IsDelivery > 0)
                    {
                        sqlRowCount.Append(" AND IsDelivery=@IsDelivery");
                        cmd.Parameters.AddWithValue("@IsDelivery", obj.IsDelivery);
                    }
                    if (obj.OrderType > 0)
                    {
                        sqlRowCount.Append(" AND OrderType=@OrderType");
                        cmd.Parameters.AddWithValue("@OrderType", obj.OrderType);
                    }
                    if (obj.OrderState > 0)
                    {
                        sqlRowCount.Append(" AND OrderState=@OrderState");
                        cmd.Parameters.AddWithValue("@OrderState", obj.OrderState);
                    }
                    sqlRowCount.Append(" AND " + DateType + " BETWEEN @BeginDate AND @EndDate");
                    cmd.Parameters.AddWithValue("@BeginDate", BeginDate);
                    cmd.Parameters.AddWithValue("@EndDate", EndDate.AddDays(1).AddMilliseconds(-2));
                }
                cmd.CommandText = sqlRowCount.ToString();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                conn.Open();
                int RowCount = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
                return RowCount;
            }
        }


        /// <summary>
        /// 审核订单
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool VerifyOrder(Int64 OrderId)
        {
            string sql = "UPDATE tb_order SET OrderState = 2 WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 打印订单
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool PrintOrder(Int64 OrderId)
        {
            string sql = "UPDATE tb_order SET OrderState = 3 WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 订单完毕
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <param name="ActualAmount">实收金额</param>
        /// <param name="DeliveryRemark">收货备注</param>
        /// <returns>True为成功，False为失败</returns>
        public bool CompleteOrder(Int64 OrderId, Decimal ActualAmount, string DeliveryRemark)
        {
            string sql = "UPDATE tb_order SET OrderState = 4,ActualAmount=@ActualAmount,ActualDeliveryDate=@ActualDeliveryDate,DeliveryRemark=@DeliveryRemark WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[4];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            parameters[1] = new MySqlParameter("@ActualAmount", ActualAmount);
            parameters[2] = new MySqlParameter("@DeliveryRemark", DeliveryRemark);
            parameters[3] = new MySqlParameter("@ActualDeliveryDate", DateTime.Now);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 订单取消
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool CancelOrder(Int64 OrderId)
        {
            string sql = "UPDATE tb_order SET OrderState = 5 WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 提交订单（主要用于客户取消的订单恢复提交）
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool SubmitOrder(Int64 OrderId)
        {
            string sql = "UPDATE tb_order SET OrderState = 1 WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 订单拒收
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool RejectionOrder(Int64 OrderId)
        {
            string sql = "UPDATE tb_order SET OrderState = 6 WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 根据订单明细总价再次更新当前订单总价
        /// </summary>
        /// <param name="orderId">订单Id</param>
        /// <param name="amount">订单Id</param>
        /// <returns>True为成功，False为失败</returns>
        public bool UpdateOrderAmountByOid(long orderId, decimal amount)
        {
            string sql = "UPDATE tb_order SET OrderAmount = @OrderAmount WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[2];
            parameters[0] = new MySqlParameter("@OrderAmount", amount);
            parameters[1] = new MySqlParameter("@OrderId", orderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }

        /// <summary>
        /// 更新付款状态为已付款
        /// </summary>
        /// <param name="OrderId">订单号</param>
        /// <returns></returns>
        public bool UpdateOrderPayByOrderId(long tradeOrderId)
        {
            string sql = "UPDATE tb_order SET IsPay = 1 WHERE OrderId=@OrderId;";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", tradeOrderId);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            return val > 0 ? true : false;
        }


        /// <summary>
        /// 根据周期条件导出CSV数据
        /// </summary>
        /// <param name="obj">订单条件对象</param>
        /// <param name="BeginDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <param name="DateType">日期类型</param>
        /// <returns>CSV数据集合</returns>
        public DataTable GetCSVData(EOrder obj, DateTime BeginDate, DateTime EndDate, string DateType)
        {
            StringBuilder sql = new StringBuilder(@" SELECT CustomerName,TelNum,Email,O.OrderId,CreateDate,DeliveryDate,
                (SELECT DeliveryPeriodName FROM tb_deliveryperiod WHERE Id=DeliveryPeriodId) AS DeliveryPeriodId,
                (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id=RegionId) AS RegionId,
                (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id=BusinessDistrictId) AS  BusinessDistrictId,
                (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id=OfficeId) AS   OfficeId,
                (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id=FloorId) AS   FloorId,
                Address,CASE OrderType WHEN 1 THEN '网络单' ELSE '' END AS OrderType,
                CASE IsDelivery WHEN 1 THEN '是' ELSE '' END AS IsDelivery,
                CASE OrderState WHEN 1 THEN '未审核' WHEN 2 THEN '已审核' WHEN 3 THEN '已派送' WHEN 4 THEN '已收单' WHEN 5 THEN '已取消' WHEN 6 THEN '已拒收' else '未审核' END AS OrderState,OrderAmount,ActualAmount,DeliveryRemark,
                CASE PaymentType WHEN 1 THEN '货到付款' WHEN 2 THEN '财付通' WHEN 3 THEN '支付宝'
                ELSE '货到付款' END AS PaymentType,CASE IsPay WHEN 1 THEN '支付成功' WHEN 0 THEN '未支付' ELSE '未支付' END AS IsPay
                FROM tb_order as O
                 WHERE 1=1");
            DataTable dt = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(GZ.DAL.MySQLHelper.connStr))
            {
                MySqlCommand cmd = new MySqlCommand();
                if (obj.Id > 0)
                {
                    sql.Append(" AND Id=@Id");
                    cmd.Parameters.AddWithValue("@Id", obj.Id);
                }
                if (obj.CustomerName.Length > 0)
                {
                    sql.Append(" AND CustomerName=@CustomerName");
                    cmd.Parameters.AddWithValue("@CustomerName", obj.CustomerName);
                }
                if (obj.Email.Length > 0)
                {
                    sql.Append(" AND Email=@Email");
                    cmd.Parameters.AddWithValue("@Email", obj.Email);
                }
                if (obj.TelNum.Length > 0)
                {
                    sql.Append(" AND TelNum=@TelNum");
                    cmd.Parameters.AddWithValue("@TelNum", obj.TelNum);
                }
                if (obj.IsDelivery > 0)
                {
                    sql.Append(" AND IsDelivery=@IsDelivery");
                    cmd.Parameters.AddWithValue("@IsDelivery", obj.IsDelivery);
                }
                if (obj.OrderType > 0)
                {
                    sql.Append(" AND OrderType=@OrderType");
                    cmd.Parameters.AddWithValue("@OrderType", obj.OrderType);
                }
                if (obj.OrderState > 0)
                {
                    sql.Append(" AND OrderState=@OrderState");
                    cmd.Parameters.AddWithValue("@OrderState", obj.OrderState);
                }
                if (obj.PaymentType > 0)
                {
                    sql.Append(" AND PaymentType=@PaymentType");
                    cmd.Parameters.AddWithValue("@PaymentType", obj.PaymentType);
                }
                sql.Append(" AND " + DateType + " BETWEEN @BeginDate AND @EndDate");
                cmd.Parameters.AddWithValue("@BeginDate", BeginDate);
                cmd.Parameters.AddWithValue("@EndDate", EndDate.AddDays(1).AddMilliseconds(-2));
                cmd.CommandText = sql.ToString();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                da.Fill(dt);

            }
            foreach (DataColumn column in dt.Columns)
            {
                if (column.ColumnName == "OrderId")
                {
                    column.ColumnName = "订单号";
                }
                if (column.ColumnName == "Email")
                {
                    column.ColumnName = "邮箱";
                }
                if (column.ColumnName == "CustomerId")
                {
                    column.ColumnName = "客户Id";
                }
                if (column.ColumnName == "CustomerName")
                {
                    column.ColumnName = "客户名称";
                }
                if (column.ColumnName == "TelNum")
                {
                    column.ColumnName = "电话号码";
                }
                if (column.ColumnName == "RegionId")
                {
                    column.ColumnName = "区域";
                }
                if (column.ColumnName == "BusinessDistrictId")
                {
                    column.ColumnName = "商圈";
                }
                if (column.ColumnName == "OfficeId")
                {
                    column.ColumnName = "写字楼";
                }
                if (column.ColumnName == "FloorId")
                {
                    column.ColumnName = "楼层";
                }
                if (column.ColumnName == "Address")
                {
                    column.ColumnName = "公司名";
                }
                if (column.ColumnName == "OrderType")
                {
                    column.ColumnName = "订单类型";
                }
                if (column.ColumnName == "IsDelivery")
                {
                    column.ColumnName = "是否送货";
                }
                if (column.ColumnName == "DeliveryDate")
                {
                    column.ColumnName = "配送日期";
                }
                if (column.ColumnName == "OrderAmount")
                {
                    column.ColumnName = "订单金额";
                }
                if (column.ColumnName == "ActualAmount")
                {
                    column.ColumnName = "实际金额";
                }
                if (column.ColumnName == "DeliveryPeriodId")
                {
                    column.ColumnName = "配送时段";
                }
                if (column.ColumnName == "CreateDate")
                {
                    column.ColumnName = "下单时间";
                }
                if (column.ColumnName == "OrderState")
                {
                    column.ColumnName = "订单状态";
                }
                if (column.ColumnName == "Memo")
                {
                    column.ColumnName = "备注";
                }
                if (column.ColumnName == "DeliveryRemark")
                {
                    column.ColumnName = "实收备注";
                }
                if (column.ColumnName == "PaymentType")
                {
                    column.ColumnName = "付款方式";
                }
                if (column.ColumnName == "IsPay")
                {
                    column.ColumnName = "支付状态";
                }
            }
            return dt;
        }

        /// <summary>
        /// 根据订单Id查询订单打印信息
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>订单打印列表</returns>
        public DataTable GetOrderPrintInfo(Int64 OrderId)
        {
            string sql = @"SELECT OrderId,CustomerName,TelNum,CreateDate,DeliveryDate,
                        (SELECT DeliveryPeriodName FROM tb_deliveryperiod WHERE Id=DeliveryPeriodId) AS DeliveryPeriodId,
                        (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id=RegionId) AS RegionId,
                        (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id= BusinessDistrictId) AS BusinessDistrictId,
                        (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id= OfficeId) AS OfficeId,
                        (SELECT DeliveryAreaName FROM tb_deliveryarea WHERE Id= FloorId) AS FloorId,
                        Address,OrderAmount,
CASE PaymentType WHEN 1 THEN '货到付款' WHEN 2 THEN '财付通' WHEN 3 THEN '支付宝'
                ELSE '货到付款' END AS PaymentType
                        FROM tb_order  WHERE OrderId= @OrderId";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
        }

        /// <summary>
        /// 根据电话信息获取订单信息
        /// </summary>
        /// <param name="tel"></param>
        /// <returns></returns>
        public DataTable GetOrderByTelNum(string tel)
        {
            string sql = @"SELECT * FROM tb_order WHERE TelNum=@TelNum and IsDelete=0 ORDER BY ID DESC LIMIT 0,1";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@TelNum", tel);
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
        }

        /// <summary>
        /// 根据订单id获取订单金额
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public decimal GetOrderAmountByOrderId(long oid)
        {
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", oid);

            decimal _amount = 0;
            using (MySqlDataReader sdr = MySqlHelper.ExecuteReader(GZ.DAL.MySQLHelper.connStr, "select OrderAmount from tb_order where IsDelete=0 and OrderId=@OrderId", parameters))
            {
                if (sdr.Read())
                {
                    _amount = Convert.ToDecimal(sdr["OrderAmount"]);
                }
            }
            return _amount;
        }



        #endregion
    }

    /// <summary>
    /// 订单明细实体类
    /// </summary>
    public class EOrderDetail
    {
        public EOrderDetail()
        {
            this.amount = 0;
            this.cnt = 0;
            this.orderid = 0;
            this.price = 0;
            this.productid = 0;
        }

        #region 属性
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        private long orderid;

        public long OrderId
        {
            get { return orderid; }
            set { orderid = value; }
        }
        private int productid;

        public int ProductId
        {
            get { return productid; }
            set { productid = value; }
        }
        private int cnt;

        public int Cnt
        {
            get { return cnt; }
            set { cnt = value; }
        }
        private decimal price;

        public decimal Price
        {
            get { return price; }
            set { price = value; }
        }
        private decimal amount;

        public decimal Amount
        {
            get { return amount; }
            set { amount = value; }
        }

        #endregion


        /// <summary>
        /// 插入明细
        /// </summary>
        /// <param name="obj">明细对象</param>
        /// <returns>成功返回True，失败返回False</returns>
        public bool Insert(EOrderDetail obj)
        {
            string sql = @"INSERT INTO  tb_orderdetail
                            ( 
                            OrderId,
                            ProductId,
                            Cnt,
                            Price,
                            Amount)
                            VALUES
                            (
                            @OrderId,
                            @ProductId,
                            @Cnt,
                            @Price,
                            @Amount);";
            MySqlParameter[] parameters = new MySqlParameter[5];
            parameters[0] = new MySqlParameter("@OrderId", obj.OrderId);
            parameters[1] = new MySqlParameter("@ProductId", obj.ProductId);
            parameters[2] = new MySqlParameter("@Cnt", obj.Cnt);
            parameters[3] = new MySqlParameter("@Price", obj.Price);
            parameters[4] = new MySqlParameter("@Amount", obj.Amount);
            int val = MySqlHelper.ExecuteNonQuery(GZ.DAL.MySQLHelper.connStr, sql, parameters);
            if (val > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 根据订单Id获得明细列表
        /// </summary>
        /// <param name="OrderId">订单Id</param>
        /// <returns>订单明细列表</returns>
        public DataTable GetOrderDetail(Int64 OrderId)
        {
            string sql = "SELECT *,(SELECT ProductName FROM tb_productinfo WHERE IsDelete=0 AND Id=ProductId)AS ProductName FROM tb_orderdetail WHERE OrderId=@OrderId";
            MySqlParameter[] parameters = new MySqlParameter[1];
            parameters[0] = new MySqlParameter("@OrderId", OrderId);
            return MySqlHelper.ExecuteDataset(GZ.DAL.MySQLHelper.connStr, sql, parameters).Tables[0];
        }
    }
}
